﻿Public Class QuanLyTienChiThang

    Private row_index As Integer = -1
    Private mapc As String = ""
    Private dt As New DataTable

    Private Sub XoaTrang()
        mapc = ""
        dateNgayChi.Value = DateTime.Now
        cbPhanLoai.Text = String.Empty
        cbTaiKhoan.Text = String.Empty
        cbNguoiChi.Text = String.Empty
        txtSoTienChi.Text = 0
        txtChiTiet.Text = String.Empty
    End Sub


    Private Sub GanText(ByVal index As Integer)
        dateNgayChi.Value = DataGridView1.Rows(index).Cells(0).Value
        cbPhanLoai.Text = DataGridView1.Rows(index).Cells(4).Value
        cbTaiKhoan.Text = DataGridView1.Rows(index).Cells(3).Value
        cbNguoiChi.Text = DataGridView1.Rows(index).Cells(2).Value
        txtSoTienChi.Text = DataGridView1.Rows(index).Cells(1).Value
        txtChiTiet.Text = DataGridView1.Rows(index).Cells(5).Value
        mapc = DataGridView1.Rows(index).Cells(6).Value
    End Sub

    Private Sub QuanLiTienChiThang_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'DataProvider.CreateConnectionStringFromXml(Application.StartupPath + "\InfoDataBaseServer.xml")

        cbThang.SelectedIndex = 0

        cbPhanLoai.DataSource = ProcFunc.ExecuteFuncSQL("select * from ChiPhi")
        cbPhanLoai.ValueMember = "MaChiPhi"
        cbPhanLoai.DisplayMember = "TenChiPhi"
        cbPhanLoai.SelectedIndex = -1

        cbNguoiChi.DataSource = ProcFunc.ExecuteFuncSQL("select * from [User]")
        cbNguoiChi.ValueMember = "MaUser"
        cbNguoiChi.DisplayMember = "TenUser"
       

        Dim str As String = "select pc.Ngay, pc.SoTien, us.TenUser, ng.TenNganHang, cp.TenChiPhi, pc.ChiTiet" &
                            ", pc.MaPhieuChi, us.MaUser, ng.MaNganHang, cp.MaChiPhi" &
                            " from PhieuChi pc, [User] us, NganHang ng, ChiPhi cp" &
                            " where pc.MaUser = us.MaUser and pc.MaNganHang = ng.MaNganHang and pc.MaChiPhi = cp.MaChiPhi" &
                            " order by pc.Ngay"
        'Dim dt As New DataTable
        Try
            dt = ProcFunc.ExecuteFuncSQL(str)
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

        If dt.Rows.Count > 0 Then
            DataGridView1.DataSource = dt
            'GanText(0)
            row_index = 0
        End If
    End Sub

    Private Sub DataGridView1_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
        If e.RowIndex < 0 Or e.ColumnIndex < 0 Then
            Return
        End If
        row_index = e.RowIndex
        GanText(e.RowIndex)
    End Sub

    Private Sub btnSoanLai_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSoanLai.Click
        XoaTrang()
    End Sub

    Private Sub btnThem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnThem.Click
        If cbPhanLoai.Text <> "" And cbTaiKhoan.Text <> "" And cbNguoiChi.Text <> "" And txtSoTienChi.Text <> "" Then
            'Tao ma phieu chi
            Dim str = "00"
            Dim day As String = str.Substring(0, 2 - dateNgayChi.Value.Day.ToString.Length) & dateNgayChi.Value.Day
            Dim month As String = str.Substring(0, 2 - dateNgayChi.Value.Month.ToString.Length) & dateNgayChi.Value.Month
            Dim year As String = dateNgayChi.Value.Year.ToString.Substring(dateNgayChi.Value.Year.ToString.Length - 2, 2)
            mapc = ProcFunc.ExecuteFuncSSQL("select max(MaPhieuChi) from PhieuChi where MaPhieuChi like N'PC" & day & month & year & "%'").ToString()
            If mapc = String.Empty Then
                mapc = "PC" + day & month & year & "0001"
            Else
                mapc = "PC" & (mapc.Substring(2, mapc.Length - 2) + 1)
            End If
            '//

            str = "insert into PhieuChi values ('" & mapc & "','" & cbNguoiChi.SelectedValue & "','" & cbTaiKhoan.SelectedValue & "','" & cbPhanLoai.SelectedValue & "','" &
                String.Format("{0:yyyy-MM-dd}", dateNgayChi.Value) & "','" & txtSoTienChi.Value.ToString & "',N'" & txtChiTiet.Text.Trim & "')"
            ProcFunc.ExecuteProcSQL(str)

            'Thêm mot dong vao datagridview khong can load lai data
            Dim row As DataRow
            row = dt.NewRow
            row(0) = dateNgayChi.Value.Date
            row(1) = txtSoTienChi.Value
            row(2) = cbNguoiChi.Text
            row(3) = cbTaiKhoan.Text
            row(4) = cbPhanLoai.Text
            row(5) = txtChiTiet.Text.Trim
            row(6) = mapc
            row(7) = cbNguoiChi.SelectedValue
            row(8) = cbTaiKhoan.SelectedValue
            row(9) = cbPhanLoai.SelectedValue

            dt.Rows.Add(row)
            DataGridView1.CurrentCell = DataGridView1.Rows(dt.Rows.Count - 1).Cells(0)
            row_index = dt.Rows.Count - 1

            MessageBox.Show("Dữ liệu đã được thêm")

        Else
            MessageBox.Show("Xin vui lòng điền đủ thông tin")
        End If
    End Sub

    Private Sub btnXoa_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnXoa.Click
        If mapc = "" Then
            Return
        End If

        Dim res As MsgBoxResult = MsgBox("Ban co muon xoa", MsgBoxStyle.YesNo, "Thong bao")
        Select Case res
            Case MsgBoxResult.Yes

                Dim str = "delete from PhieuChi where MaPhieuChi = N'" & mapc & "'"
                ProcFunc.ExecuteProcSQL(str)

                XoaTrang()
                DataGridView1.Rows.RemoveAt(row_index)

                MessageBox.Show("Dữ liệu đã được xóa")

            Case MsgBoxResult.No
                Return
        End Select
    End Sub

    Private Sub btnSua_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSua.Click
        If cbPhanLoai.Text <> "" And cbTaiKhoan.Text <> "" And cbNguoiChi.Text <> "" And txtSoTienChi.Text <> "" Then
            Dim str As String = "update PhieuChi set MaUser = '" & cbNguoiChi.SelectedValue & "', MaNganHang = '" & cbTaiKhoan.SelectedValue & "', MaChiPhi = '" & cbPhanLoai.SelectedValue & "', Ngay = '" &
                String.Format("{0:yyyy-MM-dd}", dateNgayChi.Value) & "', SoTien ='" & txtSoTienChi.Value.ToString.Trim & "', ChiTiet = N'" & txtChiTiet.Text.Trim &
                "' where MaPhieuChi = '" & mapc & "'"
            ProcFunc.ExecuteProcSQL(str)

            'Sua mot dong trong datagridview khong can load lai data
            Dim row As DataRow
            row = dt.Rows(row_index)
            row(0) = dateNgayChi.Value.Date
            row(1) = txtSoTienChi.Text.Trim
            row(2) = cbNguoiChi.Text
            row(3) = cbTaiKhoan.Text
            row(4) = cbPhanLoai.Text
            row(5) = txtChiTiet.Text.Trim
            row(6) = mapc
            row(7) = cbNguoiChi.SelectedValue
            row(8) = cbTaiKhoan.SelectedValue
            row(9) = cbPhanLoai.SelectedValue

            MessageBox.Show("Dữ liệu đã được sửa")

        Else
            MessageBox.Show("Xin vui lòng điền đủ thông tin")
        End If
    End Sub

    Private Sub cbThang_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbThang.SelectedIndexChanged
        Dim str As String = ""

        If cbThang.SelectedIndex = 0 Then
            str = "select pc.Ngay, pc.SoTien, us.TenUser, ng.TenNganHang, cp.TenChiPhi, pc.ChiTiet" &
                            ", pc.MaPhieuChi, us.MaUser, ng.MaNganHang, cp.MaChiPhi" &
                            " from PhieuChi pc, [User] us, NganHang ng, ChiPhi cp" &
                            " where pc.MaUser = us.MaUser and pc.MaNganHang = ng.MaNganHang and pc.MaChiPhi = cp.MaChiPhi" &
                            " order by pc.Ngay"
        Else
            str = "select pc.Ngay, pc.SoTien, us.TenUser, ng.TenNganHang, cp.TenChiPhi, pc.ChiTiet" &
                            ", pc.MaPhieuChi, us.MaUser, ng.MaNganHang, cp.MaChiPhi" &
                            " from PhieuChi pc, [User] us, NganHang ng, ChiPhi cp" &
                            " where pc.MaUser = us.MaUser and pc.MaNganHang = ng.MaNganHang and pc.MaChiPhi = cp.MaChiPhi" &
                            " and Month(Ngay) = '" & cbThang.SelectedIndex.ToString + "'" &
                            " order by pc.Ngay"
        End If

        Try
            dt = ProcFunc.ExecuteFuncSQL(str)
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try
        DataGridView1.DataSource = dt
        If dt.Rows.Count > 0 Then
            GanText(0)
            row_index = 0
        Else
            XoaTrang()
        End If
    End Sub

    Private Sub cbNguoiChi_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbNguoiChi.SelectedIndexChanged
        cbTaiKhoan.DataSource = ProcFunc.ExecuteFuncSQL("select distinct MaNganHang, TenNganHang from NganHang where MaUser = '" & cbNguoiChi.SelectedValue.ToString & "'")
        cbTaiKhoan.ValueMember = "MaNganHang"
        cbTaiKhoan.DisplayMember = "TenNganHang"
        'cbTaiKhoan.SelectedIndex = -1
        GanText(row_index)
    End Sub
End Class